-- =====================================================
-- Author:        Paul Bradshaw
-- Create date: 12-Nov-2006
-- Description:    Returns the iMIS Database Version string
-- =====================================================
CREATE FUNCTION [dbo].[fn_asi_DatabaseVersion](@p1 nvarchar(20))
RETURNS nvarchar(25)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @type nvarchar(20)
    DECLARE @major nvarchar(10)
    DECLARE @minor nvarchar(10)
    DECLARE @patch nvarchar(10)
    DECLARE @build nvarchar(10)
    DECLARE @version nvarchar(25)
    DECLARE @systemEntity uniqueidentifier

    SET @type = UPPER(@p1)

    SELECT @systemEntity = SystemEntityKey FROM SystemEntity WHERE SystemKeyword = 'Organization'

    SELECT @major = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Major'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
    SELECT @minor = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Minor'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
    SELECT @patch = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Patch'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity
    SELECT @build = ParameterValue FROM SystemConfig WHERE ParameterName = 'System.Database.Version.Build'
        AND OrganizationKey = '00000000-0000-0000-0000-000000000000' AND SystemEntityKey = @systemEntity

    IF @major IS NOT NULL AND @minor IS NOT NULL AND @patch IS NOT NULL AND @build IS NOT NULL
    BEGIN
        IF @type = '' OR @type = 'VERSION'
        BEGIN
            SET @version = @major + '.' + @minor + '.' + @patch + '.' + @build
        END
        ELSE IF @type = 'NOBUILD'
        BEGIN
            SET @version = @major + '.' + @minor + '.' + @patch
        END
        ELSE IF @type = 'MAJORMINOR'
        BEGIN
            SET @version = @major + '.' + @minor
        END
        ELSE IF @type = 'MAJOR'
        BEGIN
            SET @version = @major
        END
        ELSE IF @type = 'MINOR'
        BEGIN
            SET @version = @minor
        END
        ELSE IF @type = 'PATCH'
        BEGIN
            SET @version = @patch
        END
        ELSE IF @type = 'BUILD'
        BEGIN
            SET @version = @build
        END
        
    END
    ELSE
    BEGIN
        SET @version = NULL
    END

    -- Return the result of the function
    RETURN @version

END

GO
GRANT EXECUTE ON  [dbo].[fn_asi_DatabaseVersion] TO [IMIS]
GO